97

Merging Sheets

97

PopulateRow

Sub PopulateRow(ByRef ii As Double, ByVal B As String, ByVal Folder As String)

  OpenError =​ 1

  folderpath =​ Folder & B

  ‘open file

    On Error GoTo continue

    Set thisWb =​ Workbooks.Open(folderpath)

    OpenError =​ 0

continue:

  If OpenError =​ 1 Then

    MsgBox (B & “ File cannot be opened”)

  End If

x =​ 1

  If OpenError =​ 0 Then

    x =​ 1

      For Each Sheet In thisWb.Worksheets

        x =​ Sheet.Name

        y =​ WorksheetExists(x)

        If Sheet.Visible =​ True And WorksheetExists(x) Then

          On Error GoTo Nextsheet

          NumberOfRows1 =​ fileopenTargetWb.Sheets(Sheet.Name).Cells(Rows.

Count, FirstColumn).End(xlUp).Row

          NumberOfRows2 =​ FirstRow -​ 1

          NumberOfRows =​ WorksheetFunction.Max(NumberOfRows1,

NumberOfRows2)

          If NumberOfRows < FirstRow -​ 1 Then

            NumberOfRows =​ FirstRow -​ 1

          End If

          For i =​ FirstRow To FirstRow +​ RowMax

            If thisWb.Sheets(Sheet.Name).Cells(i, MandatoryColumn) =​ ““ Then

              Exit For

            End If

            For j =​ FirstColumn To FirstColumn +​ ColMax

              If thisWb.Sheets(Sheet.Name).Cells(i, j) <> ““ Then

                fileopenTargetWb.Sheets(Sheet.Name).Cells(NumberOfRows -​

FirstRow +​ 1 +​ i, j) =​ thisWb.Sheets(Sheet.Name).Cells(i, j)

              End If

            Next j

            fileopenTargetWb.Sheets(Sheet.Name).Cells(NumberOfRows -​

FirstRow +​ 1 +​ i, ColMax +​ 1) =​ thisWb.Name

          Next i

        End If

Nextsheet:

      Next

      thisWb.Close

    End If

End Sub